<?php
/* Queries for the Cleaning the category.
 * 
 
TRUNCATE TABLE `catalog_category_entity`;
TRUNCATE TABLE `catalog_category_entity_datetime`;
TRUNCATE TABLE `catalog_category_entity_decimal`;
TRUNCATE TABLE `catalog_category_entity_int`;
TRUNCATE TABLE `catalog_category_entity_text`;
TRUNCATE TABLE `catalog_category_entity_varchar`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `catalog_category_product_index`;

INSERT  INTO `catalog_category_entity`(`entity_id`,`entity_type_id`,`attribute_set_id`,`parent_id`,`created_at`,`updated_at`,`path`,`POSITION`,`level`,`children_count`) VALUES (1,3,0,0,'0000-00-00 00:00:00','2009-02-20 00:25:34','1',1,0,1),(2,3,3,0,'2009-02-20 00:25:34','2009-02-20 00:25:34','1/2',1,1,0);

INSERT  INTO `catalog_category_entity_int`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES (1,3,32,0,2,1),(2,3,32,1,2,1);

INSERT  INTO `catalog_category_entity_varchar`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES (1,3,31,0,1,'Root Catalog'),(2,3,33,0,1,'root-catalog'),(3,3,31,0,2,'Default Category'),(4,3,39,0,2,'PRODUCTS'),(5,3,33,0,2,'default-category'); 

 */
//require ("D:/vhosts/_default_/magento/app/Mage.php") ;

require_once '../app/Mage.php';
Mage::app('admin');
set_time_limit(0);
ini_set('memory_limit','6048M');

/**
 * @author Bhaumik 
 * bhaumik@maven-infosoft.com
 *
 */
class CategorySetup
{
	/**
	 * Parent Category file
	 * @var unknown_type
	 */
	protected $_inputP; //='newcat.txt';
	/**
	 * Child Category file
	 * @var unknown_type
	 */
	protected $_inputC; //='subcat.txt';
	/**
	 * db connector
	 * @var unknown_type
	 */
	protected $_db;

	/**
	 * Root category id
	 * @var unknown_type
	 */
	public $rootId;

	protected $_storeId;
	protected $_stores;

	protected $_match;
	protected $_replace;

	public function __construct()
	{
		$this->_match = array(' ','--','&quot;','!','@','#','$','%','^','&','*','(',')','_','+','{','}','|',':','"','<','>','?','[',']','\\',';',"'",',','.','/','*','+','~','`','=','"','\'');
		$this->_replace = array('-','-');
		for($i=0; $i<count($this->_match); $i++)
		{
			if(in_array($this->_match[$i],array(' ','--')))
			$this->_replace[$i] = '-';
			else
			$this->_replace[$i] = '';
		}
		//database connections
		//$this->_db = Mage::getSingleton('core/resource')->getConnection('core_write');
		$this->_db = Mage::getConfig()->getResourceModel()->getReadConnection();
	}

	public function setRootCatalog($storename = 'default')
	{
		if (empty($storename)) {
			$message = Mage::helper('catalog')->__('Skip import row, required field "%s" not defined', 'store');
			Mage::throwException($message);
		} else {
			$store = $this->getStoreByCode($storename);
		}
		if ($store === false) {
			$message = Mage::helper('catalog')->__('Skip import row, store "%s" field not exists', $storename);
			Mage::throwException($message);
		}
		$this->_storeId = $store->getId();
		$this->rootId = $store->getRootCategoryId();
	}

	public function checkInputFile($input1)
	{
		if($input1 != '' && file_exists($input1)) {
			$this->_inputP = $input1;
			return true;
		} else {
			die($input1. " - Input file not Exists!");
		}
	}

	/**
	 *  Init stores
	 *
	 *  @param    none
	 *  @return      void
	 */
	protected function _initStores ()
	{
		if (is_null($this->_stores)) {
			$this->_stores = Mage::app()->getStores(true, true);
			foreach ($this->_stores as $code => $store) {
				$this->_storesIdCode[$store->getId()] = $code;
			}
		}
	}

	/**
	 * Retrieve store object by code
	 *
	 * @param string $store
	 * @return Mage_Core_Model_Store
	 */
	public function getStoreByCode($store)
	{
		$this->_initStores();
		if (isset($this->_stores[$store])) {
			return $this->_stores[$store];
		}
		return false;
	}

	/**
	 * Save the category data
	 * @param array $importData like............
	 * "store","categories"
	 * "default","Apparel/Shoes"
	 * "default","Apparel/Clearance"
	 * "default","Electronics"
	 * Array
	 (
	 [store] => default
	 [path] => COMPUTER SYSTEMS
	 [catid] => 00
	 [etid] =>
	 )

	 *  ........................................
	 * @return unknown_type
	 */
	public function saveRow(array $importData)
	{
		$store = Mage::getModel('core/store')->load($this->_storeId);

		if (!$this->rootId) {
			return array();
		}

		$rootPath = '1/'.$this->rootId;
		if (empty($this->_categoryCache[$store->getId()])) {
			$collection = Mage::getModel('catalog/category')->getCollection()
			->setStore($store)
			->addAttributeToSelect('name');
			$collection->getSelect()->where("path like '".$rootPath."/%'");

			foreach ($collection as $cat) {
				$pathArr = explode('/', $cat->getPath());
				$namePath = '';
				for ($i=2, $l=sizeof($pathArr); $i<$l; $i++) {
					$name = $collection->getItemById($pathArr[$i])->getName();
					$namePath .= (empty($namePath) ? '' : '/').trim($name);
				}
				$cat->setNamePath($namePath);
			}

			$cache = array();
			foreach ($collection as $cat) {
				$cache[strtolower($cat->getNamePath())] = $cat;
				$cat->unsNamePath();
			}
			$this->_categoryCache[$store->getId()] = $cache;
		}
		$cache =& $this->_categoryCache[$store->getId()];


		$importData['categories'] = preg_replace('#\s*/\s*#', '/', trim($importData['categories']));
		if (!empty($cache[$importData['categories']])) {
			return true;
		}

		$path = $rootPath;
		$namePath = '';

		$i = 1;
		$categories = explode('|#|', $importData['categories']);
		

		foreach ($categories as $catName) {
			$namePath .= (empty($namePath) ? '' : '/').strtolower($catName);
			
			if (empty($cache[$namePath])) {
				$cat = Mage::getModel('catalog/category')
					->setStoreId($store->getId())
					->setPath($path)
					->setName($catName)
					->setData('sp_catid',$importData['sp_catid'])
					->setData('sp_parentid',$importData['sp_parentid'])
					->setIsActive(1)
					->setIsAnchor(1)
					->save();
				$entity_id = $cat->getId();

				$cache[$namePath] = $cat;
			}
			$catId = $cache[$namePath]->getId();
			$path .= '/'.$catId;
			$i++;
		}
		return true;
	}

	public function getPath( $catid, $name, $parent_id)
	{
		$path_ar = array();
		$path_ar[] = $name;
		if(isset($parent_id)) {
			$pid = $parent_id;
			while ($pid!=0) {
				//echo "SELECT * FROM `temp_category` WHERE id = '".$pid."'"; exit;
				$res1 = $this->_db->query("SELECT * FROM `temp_category` WHERE id = '".$pid."'")->fetchAll();
				if(count($res1)>0) {
					$path_ar[] = $res1[0]['name'];
					$pid = $res1[0]['parent_id'];
				}else {
					$pid=0;
				}
			}
		}
		$path = '';
		if(is_array($path_ar)) {
			$path_ar = array_reverse($path_ar);
			$path = implode("|#|",$path_ar);
		}
		return $path;
	}

	/**
	 * Sample line of the subcat file: catid category CPU
	 * 9995	AV Adapters	AUD-A
	 */
	public function processChild($inFile)
	{
		if($this->checkInputFile($inFile)) {
			$handle = fopen($inFile,'r');
			while (!feof($handle))
			{
				$buffer = trim(fgets($handle));
				if($buffer != '') {
					$line = explode('	',$buffer);
					$path = $this->getPath(trim($line[1]), $line[0], 1);
					//p($path);
					$etid='';
					$array = array('store' => 'default', 'categories' => $path,
					'catid' => '', 'etid' => $etid, 'cpu' => $line[2]
					,'parent_catid' => $line[0]);
					$this->saveRow($array);
				}
				//$line
			}
		}
		print "<BR/>Child Category Operation Completed!";
	}
    public function loadDataOperation($file)
	{
		//windows path seperator
		$file = str_replace("\\","\\\\",$file); 
		$dbArray = $this->_db->getConfig();
		
		$connect=mysql_connect($dbArray['host'],$dbArray['username'],$dbArray['password']);
		if (!$connect) {
			die('Could not connect: ' . mysql_error());
		}
		$db_selected = mysql_select_db($dbArray['dbname'],$connect);
		if (!$db_selected) {
			die ('Database Not Connected : ' . mysql_error());
		}
		mysql_set_charset("utf8",$connect);
		mysql_query($dbArray['initStatements'],$connect);
		
		mysql_query("LOAD DATA LOCAL INFILE
				'".$file."'  
			REPLACE INTO TABLE `temp_category`
			FIELDS TERMINATED BY ',' 
			ENCLOSED BY '\"' 
			LINES TERMINATED BY '\\r\\n'
			IGNORE 1 LINES (`id`, `name`, `parent_id`)");
		
		mysql_close($connect);
	}
    
	public function loadCategory($inFile)
	{
	    if($this->checkInputFile($inFile)) {
			$this->_db->query("DROP TABLE IF EXISTS `temp_category`;");
			$create_sql = "CREATE TABLE IF NOT EXISTS `temp_category` (
				  `id` int(10) unsigned default NULL,
				  `name` varchar(255) default NULL,
				  `parent_id` int(10) unsigned default NULL
				)";
           	$this->_db->query($create_sql);
            $file = str_replace("\\","\\\\",dirname(__FILE__));
            $filePath= $file.'\\\\'.$inFile;
            $this->loadDataOperation($filePath);
        
		}
	}
	public function processCategory()
	{
	   	$res = $this->_db->query("SELECT * FROM `temp_category`")->fetchAll();
		foreach($res as $data) {
			$path = $this->getPath($data['id'], $data['name'], $data['parent_id']);
			$array = array('store' => 'default', 'categories' => $path, 
			'sp_catid' => $data['id'], 'sp_parentid' => $data['parent_id']);
			$this->saveRow($array);
		}
		print "Category Data Import Completed!";
	}
	public function addAttribute($type, $input, $code, $attrib)
	{
		$installer = new Mage_Catalog_Model_Resource_Eav_Mysql4_Setup('core_setup');
		$installer->startSetup();
		$installer->addAttribute('catalog_category', $code , array(
        'type'              => $type,
        'backend'           => '',
        'frontend'          => '',
        'label'             => $attrib,
        'input'             => $input,
        'class'             => '',
        'source'            => '',
        'global'            => Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL,
        'visible'           => true,
        'required'          => false,
        'user_defined'      => false,
        'default'           => '0',
        'searchable'        => false,
        'filterable'        => false,
        'comparable'        => false,
        'visible_on_front'  => false,
        'unique'            => false,
        'apply_to'          => '',
        'is_configurable'   => false
		));
		$installer->endSetup();
		unset($installer);
	}
  
}
function p($ar, $exit=1)
{
	print "<pre>";
	print_r($ar);
	print "</pre>";
	if($exit == 1) 	exit;
}
function m($obj, $exit=1)
{
	print "<pre>";
	echo "Class : ". get_class($obj). "<BR/>";
	print_r(get_class_methods(get_class($obj)));
	print "</pre>";
	if($exit == 1) 	exit;
}

$obj = new CategorySetup(); //($storeId=1);

$obj->addAttribute('int', 'text', 'sp_catid', 'Richard Category Id');
$obj->addAttribute('int', 'text', 'sp_parentid', 'Richard Category Parent Id');
$obj->setRootCatalog('default');
$obj->loadCategory('sprcategory.csv');
$obj->processCategory();
unset($obj);
exit;
?>